package com.sky;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.jupiter.api.Test;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.InputStream;

/**
 * 使用POI来测试excel
 */
public class testPOI {
    @Test
    public void testWrite() throws Exception {
        // 创建xssf对象
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
        // 创建sheet
        XSSFSheet sheet = xssfWorkbook.createSheet("name-hobby");
        // 创建一行
        XSSFRow row = sheet.createRow(0);
        row.createCell(0).setCellValue("姓名");
        row.createCell(1).setCellValue("爱好");
        XSSFRow row1 = sheet.createRow(1);
        row1.createCell(0).setCellValue("张三");
        row1.createCell(1).setCellValue("篮球");
        XSSFRow row2 = sheet.createRow(2);
        row2.createCell(0).setCellValue("李四");
        row2.createCell(1).setCellValue("足球");
        // 写入文件
        FileOutputStream fos = new FileOutputStream("D:\\demo.xlsx");
        xssfWorkbook.write(fos);
        // 关闭资源
        fos.close();
        xssfWorkbook.close();
    }

    @Test
    public void testModel() throws Exception {
        // 创建xssf对象
        FileInputStream fis = new FileInputStream("D:\\demo.xlsx");
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(fis);
        // 创建sheet
        XSSFSheet sheet = xssfWorkbook.getSheet("name-hobby");
        // 创建一行
        XSSFRow row = sheet.getRow(0);
        row.getCell(0).setCellValue("姓名");
        row.getCell(1).setCellValue("爱好");
        XSSFRow row1 = sheet.getRow(1);
        row1.getCell(0).setCellValue("张三");
        row1.getCell(1).setCellValue("篮球");
        XSSFRow row2 = sheet.getRow(2);
        row2.getCell(0).setCellValue("李四");
        row2.getCell(1).setCellValue("足球");
        // 写入文件
        FileOutputStream fos = new FileOutputStream("D:\\demo01.xlsx");
        xssfWorkbook.write(fos);
        // 关闭资源
        fos.close();
        xssfWorkbook.close();
        System.out.println("测试成功");
    }


    @Test
    public void testRead() throws Exception {
        // 创建xssf对象
        FileInputStream fis = new FileInputStream("D:\\demo.xlsx");
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(fis);
        // 创建sheet
        XSSFSheet sheet = xssfWorkbook.getSheet("name-hobby");
        // 创建一行
        XSSFRow row = sheet.getRow(0);
        String name = row.getCell(0).getStringCellValue();
        String value = row.getCell(1).getStringCellValue();
        System.out.println("name = " + name);
        System.out.println("value = " + value);
        XSSFRow row1 = sheet.getRow(1);
        String name1 = row1.getCell(0).getStringCellValue();
        String value1 = row1.getCell(1).getStringCellValue();
        System.out.println("name1 = " + name1);
        System.out.println("value1 = " + value1);
        XSSFRow row2 = sheet.getRow(2);
        row2.getCell(0).setCellValue("李四");
        row2.getCell(1).setCellValue("足球");

        // 关闭资源

        xssfWorkbook.close();
        System.out.println("测试成功");
    }

    @Test
    public void testRead02() throws Exception {
        // 创建xssf对象
        FileInputStream fis = new FileInputStream("D:\\demo.xlsx");
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(fis);
        // 创建sheet
        XSSFSheet sheet = xssfWorkbook.getSheet("name-hobby");
        // 创建一行
//        XSSFRow row = sheet.getRow(0);
//        String name = row.getCell(0).getStringCellValue();
//        String value = row.getCell(1).getStringCellValue();
//        System.out.println("name = " + name);
//        System.out.println("value = " + value);

        // 循环进行遍历
        int firstRowNum = sheet.getFirstRowNum();
        int lastRowNum = sheet.getLastRowNum();
        for (int i = firstRowNum; i <= lastRowNum; i++) {
            XSSFRow row = sheet.getRow(i);
            String name = row.getCell(0).getStringCellValue();
            String value = row.getCell(1).getStringCellValue();
            System.out.println("name = " + name);
            System.out.println("value = " + value);
        }

        // 关闭资源

        xssfWorkbook.close();
        System.out.println("测试成功");
    }
}
